'use strict';

const Service = require('egg').Service;

class ScoreService extends Service {
    //添加
    async create(theory,skill,num,username) {
        let {ctx} = this;
        let time = ctx.helper.format();

        await this.app.mysql.query('insert into scorelist (username,num,theory,skill,score_time) values (?,?,?,?,?)',[username,num,theory,skill,time])
    }
    //修改
    async update(theory,skill,id){
        await this.app.mysql.query('update scorelist set theory=?,skill=? where id=?',[theory,skill,id])
    }
    //删除
    async destroy(id){
        await this.app.mysql.query('delete from scorelist where id=?',[id])
    }
    async index(pagenum,limit){
        let {ctx} = this;
        let time = ctx.helper.format();
        // let startIndex = (pagenum-1)*limit;
        return await this.app.mysql.query(`select * from scorelist where score_time=?`,[time])
    }
    //搜索
    async search(key){
        let {ctx} = this;
        let time = ctx.helper.format();
        return await this.app.mysql.query(`select * from scoreList where username like '%${key}%' and score_time=?`,[time])
    }
    //查询今日没有录入成绩的人员列表
    async notScore(){
        //1.查今日录入成绩的人员学号列表 list
        //2.在userlist表里查询学号不在上述list的人员列表
        //3.排除讲师 role_id != 3
        let {ctx} = this;
        let time = ctx.helper.format();
        return await this.app.mysql.query('select username,num from userlist where num not in (select num from scoreList where score_time=?) and (role_id != 3)',[time])
    }

    // 获取理论和技能在各个区间的人数
    async bar(min,max,type){
        console.log(min,max)
        let {ctx} = this;
        let time = ctx.helper.format(); 

        let sql = 'select count(*) from scorelist where score_time=? and ';
        sql += `${type}>=? and ${type}<=?`;

        return await this.app.mysql.query(sql,[time,min,max])
    }

    async line(num){
        let m = new Date().getMonth()+1;
        let str = `-${m}-`;
        return await this.app.mysql.query(`select * from scorelist where score_time like '%${str}%' and num=?`,[num])
    }

}

module.exports = ScoreService;
